Mega Code Archive
Excel
1) A better way to write to a range
2) A Do-Loop Until loop with IsEmpty
3) A function with a range argument
4) A name can also store the data stored in an array
5) A1 Style References
6) Accesses the PROPER( ) function through the Application WorksheetFunction object
7) AcExport
8) AcLink
9) Activate and deactivate events in a workbook
10) Activate Change
11) Activate first sheet
12) Activate last sheet
13) Activate Next Blank Down
14) Activate Next Blank To Right
15) Activate the Range
16) Activate Workbook
17) Activates the ChartObject named Chart 1
18) Activating a Window
19) Activating Only the Used Range
20) Add & format totals
21) Add a new shape
22) Add a new Sheet
23) Add a new sheet with name in A1
24) Add a new workbook and save it
25) Add a rectangle
26) Add a worksheet and append value
27) Add a worksheet and save it
28) Add calculated items to a field using the Add method of the CalculatedItems collection
29) Add Chart
30) Add Chart Sheet
31) Add Crazy Icons
32) Add data field to PivotTable
33) Add Edit Range
34) Add protection to Worksheet
35) Add the data labels using the following code
36) Add the name to the Names collection associated with the worksheet, which only includes the names that are local to that worksheet.t
37) Add user edit range
38) Add watch
39) Add Workbooks and save it as a new file
40) Adding a Chart Sheet Using VBA Code
41) Adding a Chart Title
42) Adding a Legend to the Chart
43) Adding a New Series to the chart identified by the object variable myChart, drawing the data from the range C4
44) Adding a Schema to a Workbook
45) Adding Clickable Sorting to Worksheet Lists
46) Adding Comments for a name
47) Address, a read-only property, displays the cell address for a Range object in absolute notation (a dollar sign before the column le
48) Adds a light blue shadow to the box surrounding a legend
49) Adds a line around the title and adds a glow around that line
50) Adds a PivotTable based on the data from an Access database
51) Adds major gridlines, title, and x-axis labels to an embedded chart
52) Adds two hyperlinks to active sheet
53) Adjusting Column Widths
54) Advanced Filter Demo
55) AdvancedFilter Action
56) After the reference is set, Word variables can be declared with the correct (Word) variable type
57) All properties of PageSetup
58) All the ranges that have conditional formatting set up
59) An Example of Early Binding
60) Append a worksheet to the end of the worksheet list
61) Application ExecuteExcel4Macro
62) Applying chart formatting
63) Arranges the open workbooks in a tiled configuration
64) Arranging and Resizing Windows
65) Array to Columns
66) Array to rows
67) Array to rows - method (Limited to 65,536)
68) Assign a value to the Orientation property of the PivotField object, as shown here
69) Assign sub routine to ActiveSheet OnEntry
70) Assign the column width of one cell to another cell on the active sheet, using
71) Assign the value in C1 in the active sheet to D10 in the sheet named Sales, in the active workbook
72) Assign the value of a property to a variable so it can be used in later code
73) Assign the value to the ColumnWidth property of the ActiveCell using
74) Assigns the values 101 to 200 to the range B1
75) Assing value in Cells to array, do the bubble sort and assign them back
76) Automatically generating a chart without user interaction
77) Available Constants for the Type Argument of the AutoFill Method
78) Background and Foreground color
79) Before Save event
80) BeforeClose event is used to delete a custom menu
81) BeforeDoubleClick event
82) BeforeRightClick event
83) Bold cells in a Row
84) Building the Table
85) Calculate a moving average
86) Calculate cell
87) Calculating a mortgage payment
88) Call worksheet function directly
89) Calling CurrentRegion to Inspect a Lists Useful Characteristics
90) Cancel a change in Worksheet Selection Change event
91) Cancel Close action based on the A1 value
92) Cells Find
93) Cells(Rows Count, A) End(xlUp) Select
94) Center workbook
95) Change cell A1 and move to cell A2 and change it
96) Change column width
97) Change range border color
98) Change range border style
99) Change row height
100) Change the name of the active worksheet
101) Change the range color in selection change event
102) Change the selected cell format
103) Change the Value property for a range of any size
104) Changes a cells interior color to red when it is double-clicked
105) Changes color of numbers 100
106) Changes font to bold
107) Changes the value of the Name property of the first worksheet in the first workbook of the Excel application
108) Changing a Chart Title Using VBA
109) Changing Boolean settings
110) Changing the Bevel and 3-D Format
111) Chart double click event
112) Chart_Deactivate procedure also displays a message only when the chart sheet is deactivated
113) Check Name existance
114) Check the existence of ActiveCell
115) Check Whether a Sheet in an Open Workbook Exists
116) Check Whether a Workbook Is Open
117) Checking for the Existence of a Name
118) Checking the Status of All the Links in a Workbook
119) Checks each changed cell and displays a message box if the cell is within the desired range
120) Checks the UserName; if it is not Admin, this code protects each sheet from user changes
121) Checks values in a range 10 rows by 5 columns
122) Checks values in a range 10 rows by 5 columns with nested for loop
123) Clean the comment
124) Cleans up every selected cell
125) Clear NonDate Cells
126) Clear password
127) ClearContents method deletes the contents of the range but leaves the formatting intact
128) ClearFormats method deletes the formatting in the range but not the cell contents
129) Clearing a range differs from deleting a range When you delete a range, Excel shifts the remaining cells around to fill up the range
130) Close a workbook and save it
131) Closes the active workbook without saving changes
132) Closing All Open Workbooks
133) Code does does not use With-End With
134) Color cells
135) Color multiple-column ranges
136) Coloring all negative cells backgrounds red
137) Compatibility Mode
138) Constants for the Value Argument for the SpecialCells Method
139) Controlling Worksheet Visibility
140) Convert an existing chart to use arrays instead of cell references and make it independent of the original data
141) Converts docstring column into comments on name column
142) Converts the Integer to a String representation of the same column
143) Copies all the numeric constants in the active sheet to blocks in the sheet named Constants, leaving an empty row between each block
144) Copies the contents of range A1
145) Copy a range
146) Copy and Paste can be carried out entirely by the Copy method
147) Copy areas
148) Copy cells
149) Copy data from one sheet in a range to another sheet
150) Copy method can use an argument for the destination range
151) Copy method can use an argument that represents the destination for the copied range
152) Copy method has one argument - the destination range for the copy operation
153) Copy the third worksheet before the 2nd worksheet
154) Copy the third worksheet to a new book
155) Copy the value from the variable into the cell on the right
156) Copying a variably sized range
157) Copying or Moving a Worksheet
158) Count a range
159) Count Comments
160) Count property returns the number of cells in a range (all cells, not just the nonblank cells) Its a read-only property
161) Count the blank elements in a range
162) Count the Number of Workbooks in a Directory
163) Counts columns in a multiple selection
164) Create a function for Excel
165) Create a global name that refers to a range using the Add method of the Workbook objects Names collection
166) Create a local name
167) Create a name based on a Range
168) Create a new sheet with month name and year as its name
169) Create a new workbook
170) Create a set of related formulas in a column
171) Create a shape and set its color
172) Create a workbook and save it as a new file
173) Create Pivot Table From database
174) Create your own Save dialog in the event
175) Creates a hyperlink to each worksheet in the workbook excluding the worksheet containing rgLinks
176) Creates a new instance of Word and then opens and makes visible an existing Word document
177) Creates a new PivotChart
178) Creates a new workbook and adds it to the collection, reads the number of workbooks into a variable, and selects all worksheets in t
179) Creates the chart with absolute reference and format it
180) Creates the formatting shown in column A
181) Creates web page with interactivity from an embedded chart
182) Creating a Chart
183) Creating a Chart on an Existing Worksheet
184) Creating a Chart Using the Chart Object
185) Creating a Database Form From an Excel Worksheet in worksheet activate event
186) Creating a Named Range from Workbooks
187) Creating a New Blank Workbook
188) Creating a New Chart Using the ChartWizard Method
189) Creating a New Series, use the NewSeries method with the SeriesCollection collection
190) Creating a New Workbook Based on a Template
191) Creating a New Workbook Based on an Existing Workbook
192) Creating a new worksheet for your workbook, and then it fills in several cells in that new worksheet
193) Creating a PivotTable Report
194) Creating an Embedded Chart
195) Creating and Referencing Objects
196) Creating Charts
197) Creating Your Own XSD
198) Custom Sort Order
199) Data Lists
200) Declare a Workbook object variable and assign the ActiveWorkbook object to it, so that subsequent code can work with the object vari
201) Define a name by hard code the cell address
202) Define a name by referring a workbook
203) Define a name by referring the selection cell address
204) Define a string type variable and set to Cell(1,D)
205) Defined a print area
206) Defines a Chart object variable and a ChartGroup object variable and then stops
207) Delete a shape
208) Delete all shapes
209) Delete empty rows
210) Delete numbers
211) Delete the range name
212) Deletes a range and then fills the resulting gap by shifting the other cells to the left
213) Deletes all the numbers in a worksheet, leaving the formulas intact
214) Deleting a Named Range
215) Determine whether a ListObject object AutoFilter is turned on or off by testing its ShowAutoFilter property
216) Determines if a given worksheet name exists in a workbook
217) Determines the last row and column in the worksheet
218) Determining a cells data type
219) Determining a charts source data
220) Determining the number of printed pages
221) Determining the type of selected range
222) Determining whether a range is contained in another range
223) Determining which Names Overlap a Range
224) Disable the events and then reenable them at the end of the procedure
225) Display auto shapes
226) Display range dialog
227) Displaying Excels Built-in Dialog Boxes
228) Displays a message box that shows the value in cell A1 on Sheet1
229) Do Loop Until Loops with Worksheets
230) Do the Advanced Filter
231) Does ComboBox contain data
232) Do-Until loop and IsEmpty
233) Do-While loop with ActiveCell
234) Drag the third worksheet to the first sheet position
235) Draw a star
236) Dynamic Arrays for worksheet name
237) Early vs Late Binding
238) Embedded Chart Mouse Events
239) Enable Events
240) Enables access to named ranges in arbitrary workbooks
241) Entering a Formula in a Cell
242) Entering a value in the next empty cell
243) Entering A1 Versus R1C1 in VBA
244) Entering Text in the Current Cell
245) Enters a value in 10 cells in a column and then sums the values
246) Event procedure runs every time the worksheet recalculates
247) Events with Chart Sheets
248) Events with Embedded Charts
249) Example that activates cell A1 whenever the sheet is activated
250) Examples of Activation Events
251) Excel based database
252) Excel has built-in options to read files where fields are delimited by tabs, semicolons, commas, or spaces
253) Export to XML file
254) Exporting a range to a text file
255) Exporting to an XML File
256) Exporting XML
257) Exports the active chart as a GIF file
258) Expression refers to a cell one row below cell A1 and two columns to the right of cell A1
259) Extending an Existing Series in the chart identified by the object variable myChart using the data in the cells P3
260) Files in the Same Directory
261) Fill data to ComboBox
262) Fill formula to cell
263) Fill named range with values
264) Filter a column to one of two customers, joined by the OR operator
265) Filtering Based on Color or Icon
266) Filters to show records
267) Find in a range
268) Find next heading
269) Find out which names are overlapping the selected cells, regardless of whether they entirely contain the selected cells
270) Finding the maximum value in a range
271) For a continuous break
272) For a new-column break
273) For a new-page break
274) For an even-page break
275) For an odd-page break
276) For Each Loops for a range
277) For Each-Next is useful for cycling through each member of a collection
278) For Next Loop with Worksheet
279) Format Axis, Title font size and color
280) Format current region
281) Format date on report
282) Format sheets
283) Format Soft Edges With Loop
284) Format(ALL LOWERCASE , )
285) Formats the trendline for the first series in a chart
286) Formatting a Basic Chart
287) Formatting a border
288) Formatting Cells in the Bottom 5
289) Formatting Cells in the Top 10
290) Formatting Cells in the Top 2 percent
291) Formatting Cells whose value between 10 and 20
292) Formatting Duplicate Cells
293) Formatting Range
294) Formatting Unique Cells
295) Formulas Protection
296) From Access to Excel
297) Generate named range from Range object
298) Generate Range object from named range
299) Generates a three-color color scale in range A1
300) Get active workbook name only(without path name)
301) Get cell value
302) Get Chart SeriesCollection value
303) Get Chart sheet
304) Get current active workbook full name
305) Get Embedded Charts
306) Get next file name
307) Get path of current work book
308) Get rows from a named range (name the range before running this cript)
309) Get Rows from a range
310) Get Schema
311) Get selection address
312) Get shape and output its z order
313) Get the address of ActiveCell
314) Get the address of selection
315) Get the comment text
316) Get the Excel version
317) Get the last cell
318) Get the last row and last cell
319) Get work book from same directory
320) Get workbook format
321) Get Worksheet name
322) Get Worksheets count in a Workbook
323) Getting a Filename from a Path
324) Gives indexes of sheets
325) Go to a name
326) Go to the Max
327) Granted permission to two users
328) Group sheets together
329) Grouping Worksheets
330) HasFormula property
331) Hide a name by setting its Visible property to False
332) Hide All Toolbars
333) Hide the name after it has been created
334) Hides the legend when it is double-clicked; double-clicking either axis brings back the legend
335) Hides the worksheet
336) Hiding and Unhiding Worksheets
337) Highlight cells above average
338) Highlight cells below average
339) Highlight the Entire Row for the Largest Sales Value
340) Highlights all cells that contain a capital letter A
341) Highlights all dates in the past week
342) Highlights selected range
343) Identifies all the cells that contain formulas resulting in errors in the active worksheet
344) Identifying Row with Largest Value in G
345) If multiple versions of Word are installed, you can create an object for a specific version The following statement, for example, us
346) If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range.t
347) If the Range object consists of more than one cell, the Row property returns the row number of the first row in the range
348) If the Range object is not in the active worksheet in the active workbook
349) If the value of the active cell is too big, change it
350) If you want Excel to stop prompting you to save changes you didnt make
351) If you want the same data to appear on other sheets but not in the same cell addresses
352) If you want to make sure that all changes are saved when the workbook closes
353) If you want to prevent users from printing only particular sheets in your workbook, use this similar code instead
354) Import a file
355) Importing a text file to a range
356) Importing an XML Data File
357) Importing Data into an Existing XML Map
358) In most cases you can avoid using the worksheet Index property
359) Increases font size of the selected cells
360) Insert a row to a worksheet by using the SQL statement
361) Insert chart sheets after each worksheet
362) Insert hidden name
363) Insert the values directly as the function arguments
364) Insert two chart sheets after the last worksheet in the active workbook The chart sheets receive default names, such as Chart1 and C
365) Inserts a chart before each sheet
366) Inserts a chart before each sheet 2
367) Inserts a continuous section break before the second paragraph in the active document
368) Inserts a formula in cell A11 of a worksheet that calculates the sum of the values in the range A2
369) Inserts a new sheet after each sheet
370) Inserts new monthly sheet
371) Inserts the sheet names into the SheetNames array
372) Is a cell empty
373) Is active cell empty
374) Is in last column
375) Is in last row
376) Is Name In Workbook
377) Is selected file open
378) Is the current selection a Range
379) Is Workbook Open
380) Late Binding
381) Leveraging DOM and XPath to Manipulate XML Files
382) Line style
383) Link Status Checker
384) Linking a Spreadsheet
385) Linking an Excel Spreadsheet
386) Load excel file
387) Loop through a range cell by cell
388) Loop through all cells in a range
389) Loop through all worksheets in workbook and reset values in a specific range on each sheet
390) Loop through each series in chart and alter marker colors
391) Looping through a Collection
392) Looping through a Collection of Shapes
393) Looping through a worksheet range using a ForNext loop
394) Looping through the ChartObjects collection
395) Looping Through Worksheets in a Workbook
396) Macro for changing page setup to landscape orientation
397) Magic Squares
398) Make a Cell font bold based on the cell value
399) Make a range autofit
400) Make column headings bold
401) Make text in first column bold
402) Make the font in number cell bold
403) Makes cell background red if the value is negative
404) Makes use of conditional formatting and overwrites any existing conditional formatting on the sheet
405) Manipulating Charts
406) Maximizes any workbook when it is activated
407) Minimizes the workbooks window
408) Minute(time) returns a VariantInteger between 0 and 59, inclusive, representing the minute for time
409) Modify multiple cells at once using a range reference (like A1
410) Modifying chart properties
411) Modifying Pivot Tables
412) Modifying the chart type
413) Monitoring a specific range for changes
414) Month(Date)
415) Month(date) returns a VariantInteger between 1 and 12, inclusive, representing the month for date
416) Month(Feb 4, 1900) returns 2
417) Move sheet to the end
418) Move the active cell by using ActiveCell Offset
419) Move the second worksheet to end of workbook
420) Move to the new cell, you need to use the Activate or Select method
421) Moves the active cell up two rows (RowOffset
422) Moves the worksheet named Homes from the workbook named Planning xls to the workbook named Building Schedule xls, inserting the work
423) Moving a range
424) Moving the pointer to the cell containing the greatest value
425) Moving to Other Cells
426) Names Overlapping Selection
427) Nest If statement in Do Loop Until for cells
428) Nest If statement in Do Loop While with comparison operators
429) Noncontiguous ranges
430) Only allow the workbook to close if the figure in A1 is between 500 and 600
431) Open a text file for workbook
432) Open a workbook and then size it to fit just within the application window
433) Open the Excel worksheet, create a recordset with the data in the sheet, and then print it in the Immediate window
434) Open workbook by name
435) Open Workbook file (xls file)
436) Opening a New Window on a Workbook
437) Opening an Excel Spreadsheet with ADO
438) Opening Delimited Files Simplified
439) Opening Fixed-Width Files
440) Opens a workbook named MyWorkbook xls located in the same directory as the active workbook An error is generated if the file cannot
441) Opens the text file named Data13 txt in the folder z
442) Opens the workbook named Plan xls stored in the D
443) Overridden with the Destination parameter
444) Overwriting an Existing Workbook
445) Pass value between events
446) Payment Function
447) Performs a simple bubble sort to sort the worksheets in the workbook
448) PivotTables Collection
449) Place the value (result) of a formula into a cell rather than the formula
450) Places the username in the footer of each sheet printed
451) Presents user with a GetOpenFileName dialog that allows multiple file selection
452) Preventing data validation from being destroyed
453) Preventing Users from Inserting More Worksheets
454) Previewing a workbook before saving it as a Web page
455) Printing a Worksheet
456) Prints the first two pages of the worksheet named Summary in the workbook named Planning xls to a file named Planning Summary prn in
457) Procedure is executed when the workbook is closed
458) Process the formulas
459) Produces the top 10 revenue records
460) Producing an Excel worksheet on a Word 2000 document
461) Programmatically Changing XML Map Properties
462) Programmatically Retrieving Link Source Information
463) Proper syntax for SoftEdge
464) Protect and share
465) Protect Chart
466) Protect Sheet
467) Protect workbook
468) Protected cell error
469) Protecting a Worksheet
470) Protecting Worksheet Assets with the Protect Method
471) Protects the myWorksheet worksheet with the same password but allows the formatting of cells and allows the sorting of unlocked cell
472) Protects the structure and windows of the active workbook with the password 0llsecurd
473) Providing Dynamic Scaling to Your Worksheets
474) Puts the filename in the right footer
475) R1C1 Style
476) R1C1 Style References
477) Range objects cannot be used with an worksheet object as superobject
478) Read a range from InputBox
479) Read the Value property only for a single- cell Range object
480) Redefines the layout of the fields in the existing Table, apart from the data field
481) Refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook
482) Refer to an entire column
483) Refer to an entire row
484) Refer to the B2 cell in DataInput, while another workbook is active
485) Reference a range by name
486) Reference a worksheet from a workbook
487) Reference Char object from ActiveSheet
488) Reference worksheet across worksheet
489) Reference worksheet and range by name
490) Referencing Charts and Chart Objects in VBA Code
491) Refresh Data
492) Refresh Your XML Data
493) Remove Edit Ranges
494) Remove sharing
495) Remove the CalculatedItem by deleting it from either the CalculatedItems collection or the PivotItems collection of the PivotField.t
496) Remove user
497) Remove user edit range
498) Removes all borders for the selected cells
499) Renames an existing name
500) Repeating Actions with a Loop
501) Reset value in a range to zero
502) Resize Comments
503) Restore Toolbars
504) Retrieving data point labels from field names in the worksheet
505) Retrieving Values Stored as a Workbook Name Using the Evaluate Method
506) Return cell value in a range as an array
507) Returning the Last Used Cell in a Column or Row with Worksheet
508) Returns a count of all cells in a selection
509) Returns a count of non-blank cells in a selection
510) Returns a count of the number of columns in a selection
511) Returns a count of the number of rows in a selection
512) Returns a number that represents the last nonempty cell in the same row
513) Returns a range object that represents the last non-empty cell in the same row
514) Returns a workbook object with the XML data mapped to your spreadsheet
515) Returns all customers that started with the letters A through E
516) Returns the text in the active cell in the first window open on the workbook named Planning xls
517) Returns the type of a range in an area
518) Returns True if rng1 is a subset of rng2
519) Row property returns the row number of a single-cell range
520) Safely Deleting Worksheets Using the DeleteSheet Function
521) Save as the active workbook
522) Save changes automatically, specify this as a parameter of the Close method
523) Save the active workbook
524) Save workbook and close
525) Saves the open workbook named Schedule xls under the name Building Schedule xls in the folder named server2Public using the Microsof
526) Saves the worksheet range $A$1
527) Saving a Workbook That Has Already Been Saved
528) Saving All Open Workbooks
529) Saving Worksheets as Web Pages
530) Seaching for Charts Using the Chart Title
531) Searching for a Name
532) Searching for the Name of a Range
533) See VBA Immediate window for a list of permissions and users
534) Select a chart object
535) Select a range A1
536) Select a range and activate another
537) Select a range by using the ActiveCell
538) Select Active Area
539) Select active cell and up to the end
540) Select active column
541) Select active row
542) Select ActiveCell and cells to its Left
543) Select ActiveCell and cells to its Right
544) Select ActiveCells range
545) Select Comment Cells
546) Select current range and copy
547) Select Current Region
548) Select entire column
549) Select entire row
550) Select Entire Sheet
551) Select every other row and apply the formatting
552) Select first column to last column
553) Select First To Last In Column
554) Select First To Last In Row
555) Select first to last row
556) Select Last Cell
557) Select named range
558) Selecting a Dynamic Date Range Using AutoFilters
559) Selecting Multiple Values from a Filter
560) Selecting with SpecialCells
561) Selection Entirely In Names
562) Selection move down
563) SelectionChange event executes whenever the user makes a new selection on the worksheet
564) Selects cell G4
565) Set application key in Workbook Open event and BeforeCloseEvent
566) Set Axes gridline and its border color
567) Set cell color
568) Set cell value with For Loop
569) Set center footer to your string text
570) Set data to cell D1 of the selected worksheet And format its contents with color and borders
571) Set Encryption
572) Set Lengend position and ChartArea Interior Color
573) Set PlotArea LineStyle, border color, width and height
574) Set the center footer to file path
575) Set the color for whole range
576) Set the color row by row
577) Set the underline, color and font name
578) Set the value in a range by cell index
579) Set Workbook Password
580) Sets just the color of cell C1 to red
581) Sets to True the Bold property of the Font object contained in the Range object
582) Sets up a two-color gradient using two theme colors
583) Setting Excel to Remove Personal Information from the File Properties When You Save
584) Setting Passwords and Read-Only Recommendation for a Workbook
585) Setting Up Conditional Formats in VBA
586) Shade every second row
587) Shade every second row by using the Do Loop
588) Shade every second row by using the Do Until Loop
589) Sharing a Workbook
590) Show chart
591) Show Edit Ranges
592) Show Encryption Properties
593) Show Protection Properties
594) Show the workbook protection
595) Show User Permissions
596) Some Worksheet event are executed before the associated event occurs and have a Cancel parameter that is passed by reference
597) Sort all worksheets
598) Sort fields
599) Sort sheets
600) Specify colors with VBAs RGB function
601) Specify Exact Location
602) Specify that the Names collection belongs to a worksheet
603) Specifying an Icon Set
604) Specifying Individual Cells with the Cells Property
605) Specifying Ranges for Each Icon
606) Specifying the Chart Type
607) Specifying the Source Data for the Chart by using the SetSourceData method of the Chart object
608) Split windows
609) Standard Workbook Properties
610) Store formulas into names The formula must start with an equals sign (=)
611) Store the location of the active cell and then return it to the stored location
612) Stores the current SheetsInNewWorkbook property, sets the SheetsInNewWorkbook property to 12, creates a new workbook (with those 12
613) Storing a formula in a name is the same as for a range
614) Storing Values in Names
615) StrConv(ALL LOWERCASE , vbLowerCase)
616) Strolling Through the Color Palette
617) Sum a Range
618) Sum Cells Based on the Interior Color
619) Sum the elements in a range
620) Sum the range
621) Swap Text With Cell On Right
622) Take the current text value, and add the message INVALID
623) Tells you how many workbooks are open
624) Test for empty cell
625) Test the effect of the Option Base statement
626) TextToColumns Example
627) The Add method allows you to specify a template for the new workbook
628) The AutoFilter object only exists when the AutoFilter feature is turned on You can determine whether the Worksheet AutoFilter is act
629) The BeforeClose event for a workbook
630) The BeforeSave event for a workbook
631) The BubbleSort() procedure sorts a column of integer values from lowest to highest value
632) The Cells property is best suited for use with loops because it accepts numerical parameters representing the row and column indices
633) The Change event
634) The Chart_Select procedure listed next is executed whenever an item on the chart is selected
635) The Clear method
636) The Column property returns the column number of a single-cell range
637) The Copy and Paste methods
638) The distinction between the Workbook and Window objects lies in an additional method that can be used to create a Window object
639) The Evaluate method can also be used with arrays
640) The event-handler procedure for the SelectionChange event
641) The fastest way to delete rows is provided by Excels AutoFilter feature
642) The font color of all cells in the active worksheet is set to red
643) The Font property
644) The Interior property
645) The NumberFormat property represents the number format (expressed as a text string) of the Range object
646) The Offset method can also use negative arguments A negative row offset refers to a row above the range A negative column offset ref
647) The OLEObjects Collection
648) The Sheets Collection
649) The Text property returns a string that represents the text as displayed in a cell
650) The Workbooks property is a member of the Application object and returns a reference to the Workbook object specified by the index v
651) There is a Filters collection associated with the AutoFilter object that holds a Filter object for each field in the AutoFilter
652) To add a range name based on a selection
653) To add a range name based on a selection using a variable
654) To add a range name for known range
655) To add an existing workbook file to the Workbooks collection, you use the Open method
656) To apply a read-only recommendation to a workbook, set its ReadOnlyRecommended property to True
657) To apply one of the 3-D rotation presets to a chart element
658) To avoid confusion, you can select a workbook unambiguously- if you know the name of the desired Workbook object
659) To change chart default title, set the Text property of the ChartTitle object, which represents the chart title
660) To clear the filter from the customer colum
661) To close a single workbook, use the Close method of the Workbook object The Close method accepts three optional arguments (SaveChang
662) To close all open workbooks, use the Close method of the Workbooks collection object
663) To control orientation
664) To copy a range to a different worksheet or workbook, simply qualify the range reference for the destination
665) To discard any changes to the workbook
666) To ensure that a chart is selected, you can add a statement to determine if a chart is active
667) To ensure that the worksheet AutoFilter is turned off
668) To enter the name Florence into cell C10, you assign the name to the Value property of the Range object
669) To fill the bars of a data series with a picture
670) To find records that have a particular fill color, use an operator of xlFilterCellColor and specify a particular RGB value as the cr
671) To find records that have a particular font color, use an operator of xlFilterFontColor and specify a particular RGB value as the cr
672) To find records that have no conditional formatting icon, use an operator of xlFilterNoIcon and do not specify any criteria
673) To find records that have no fill color, use an operator of xlFilterNoFill and do not specify any criteria
674) To format the text of the title, work with its Font object
675) To hide specific worksheet
676) To link that tabs command bar to a right-click in any cell, enter the following code in the private module of ThisWorkbook
677) To make a specific worksheet very hidden
678) To position the title, set its Top property (specifying the number of points from the top edge of the worksheet) and its Left proper
679) To print a defined area, center horizontally, with 2 rows as titles, in portrait orientation and fitted to page wide and tall - 1 co
680) To print header, control the font and to pull second line of header (the date) from worksheet
681) To print several ranges on the same sheet
682) To refer to a range in a different workbook
683) To save a workbook from a VBA program, use either the Save methods of the Workbook object
684) To see if the workbook is already a member of the Workbooks collection
685) To select a Workbook object from the Workbooks Collection object
686) To select the last Workbook object in the collection
687) To set a password to modify, set the WritePassword property of the Workbook object
688) To set the visible property to True on ALL sheets in workbook
689) To set the visible property to xlVeryHidden on ALL sheets in workbook
690) To switch off a range AutoFilter, you use the AutoFilter method of the Range object with no parameters
691) To toggle between hidden and visible
692) To unhide a specific worksheet
693) To use the Copy function and Paste method, first insert the formula in the original cell as before, execute the Copy function of the
694) To validate user input, one possible location for the code is the SheetChange() event procedure of the Workbook object
695) Toggle Comments
696) Toggles between normal, bold, italic and bold+italic
697) Toggles text wrap alignment for selected cells
698) Toggles the Visible property of the Text Box
699) Toggling a Boolean property
700) Trace worksheet activate event
701) Track worksheet change event
702) Tracking cell changes in a comment
703) Transferring one-dimensional arrays with build-in function
704) Transposes the values of a group of cells in a worksheet
705) Transposing is taking a rectangular block of data and rotating it so that columns become rows and vice versa
706) Turn onoff the AutoFilter drop-downs
707) Turn rowcolumn headers and gridlines onoff
708) Turns off the drop-downs for Columns C, E, F, G, and H
709) Two ways you can use Evaluate to generate a reference to a Range object, and assign a value to that object
710) Understanding Chart Events
711) Unhiding Every Worksheet in a Workbook
712) Unprotect Chart
713) Unprotect Workbook
714) Unprotect worksheet
715) Updating Links with a New File Location
716) Use [] to evaluate formula
717) Use 0 as one or both of the arguments for Offset
718) Use a Change event to take what is in the cell and insert the colon for you
719) Use a String rather than an index value to reference a workbook
720) Use ActiveCell and ActiveCell End to create Range
721) Use ActiveCell End to select a range
722) Use ActiveCell Offset to move the curren selection
723) Use ActiveCell SpecialCells(xlLastCell)
724) Use ADO to read the data from Access database to Excel
725) Use all the ordinary numeric operators, like +, -, , , and ^
726) Use Application object to save a workbook as a new file
727) Use arrays to fill ranges faster
728) Use case select statement to call common functions
729) Use Copy and Paste or AutoFill functions
730) Use disgonal cells to select a range
731) Use Do Loop While to change ActiveCell value
732) Use Evaluate function to execuate function
733) Use Event Parameters to cancel an event
734) Use For Each to loop through all chart objects
735) Use For loop to fill a range
736) Use for loop to loop through all worksheets
737) Use For Next loop through Workbooks
738) Use fully qualified properties
739) Use function from Excel
740) Use Intersect when you want to find the cells that are common to two or more ranges, or in other words, where the ranges overlap
741) Use if, ElseIf and Else with ActiveCell
742) Use loop to select cells
743) Use names to store numbers between sessions
744) Use object variables to represent the ranges
745) Use row and column index to reference cell
746) Use the AutoFill function by specifying the destination range
747) Use the BeforePrint event to add information to a header or footer before the sheet is printed
748) Use the cell reference directly
749) Use the Evaluate method equivalent to evaluate the name in VBA
750) Use the LARGE function to determine the kth-largest value in a range
751) Use the Range and Cells properties of the Worksheet object to return a Range object
752) Use the Range property as an argument within another Range property
753) Use the return value of the Add method to create an object variable that refers to the new workbook
754) Use the Transpose function to turn the one column into one row
755) Use this code to turn off the AutoFilter drop-downs
756) Use Union when you want to generate a range from two or more blocks of cells
757) Use with statement with worksheet
758) Uses nested ForNext loops to count the total number of cells used in all open worksheets
759) Uses the EntireColumn property, which returns a Range object that consists of a full column
760) Uses the Not operator to effectively toggle the page break display from True to False and from False to True
761) Using a Function to Check for the Existence of a Code Name
762) Using a lookup function
763) Using DOM with ADO to Convert Excel Data to XML
764) Using Error Handling When Using SpecialCells
765) Using Excel Application Functions in VBA
766) Using named ranges of cells
767) Using Replace Programmatically to Set the Correct Range
768) Using the Change Event to Respond to Worksheet Changes
769) Using the CreateObject Function to Create a Late-Bound Instance of Excel
770) Using the End Property to Navigate within a Worksheet
771) Using the Follow method of the Hyperlink object(user selecting the hyperlink in the Excel application)
772) Using the Interior Object to Alter the Background of a Range
773) Using the Intersect Method to Create a New Range from Overlapping Ranges
774) Using the Names Object to List All Named Ranges
775) Using the New NumberFormat Property
776) Using the Parent Property to Obtain a Reference to an Objects Parent Object
777) Using the Range Property to Refer to Groups of Cells
778) Using the SpecialCells method
779) Using the Union Method to Join Multiple Ranges
780) Using Two Colors of Data Bars in a Range
781) Validating data entry in Worksheet change event
782) Validation with a Spreadsheet Cell
783) Value is the default property for a Range object
784) Values for the UpdateLinks Argument
785) Visible Property
786) When you need to step through several objects in a collection, use a loop and a looping variable to represent the index of the objec
787) Widen first column to display text
788) With each range selection you make in the worksheet, the background color of the selection will turn blue
789) Work your way through the entire object hierarchy to set one property
790) Workbook activation events
791) Workbook Events Open
792) Workbook open event
793) Workbook permissions removed -- workbook access is no longer restricted through IRM
794) Workbook permissions restricted
795) Workbook_BeforePrint
796) Workbook_SheetSelectionChange
797) Working with a Chart Axis
798) Working with the ActiveWorkbook Object
799) Working with the Region around the Active Cell
800) Working with Worksheets to add a worksheet
801) Works on all the chart sheets in the active workbook
802) Worksheet Change Events
803) Worksheet_Calculate()
804) Worksheet_SelectionChange(ByVal Target As Range)
805) Write a formula in the first 10 columns of row 21 in a worksheet that calculates the standard deviation of the values in rows 2 thro
806) Write Password
807) XlExcel9795 Saves Document As An Excel workbook for Excel versions 95 and later
808) XlHtml Saves Document As A web page
809) XlNormal
810) XlTemplate Saves Document As A template
811) XlWBATExcel4IntlMacroSheet
812) XlWBATExcel4MacroSheet
813) XlWBATWorksheet
814) XlWebArchive Saves Document As A single file web page
815) XlXMLSpreadsheet
816) XML transform
817) You can move a range with a single VBA statement
818) Zooms in on a left mouse click and zooms out on a right mouse click